create or replace package app_apex is

  -- Author  : LEV ERUSALIMSKIY
  -- Created : 04/12/2011 12:38:37 PM
  -- Purpose :
 function getRegionId(p_static_region_id varchar2) return varchar2;

 FUNCTION render_dtreetable(
    p_region              IN apex_plugin.t_region,
    p_plugin              IN apex_plugin.t_plugin,
    p_is_printer_friendly IN BOOLEAN )
  RETURN apex_plugin.t_region_render_result;

  procedure setMsgValue(p_result_id number, p_val varchar2);
  procedure getProgress(p_result_id number);

  function  showDetails(p_id number) return number;
  procedure showHeader(p_id number);

  function  htmlStr(v_str varchar2) return varchar2;

  procedure setCancel(p_result_id number, p_val varchar2);
  function  isCancel(p_result_id number) return boolean;

end app_apex;
/

create or replace package body app_apex is

 gc_cr   varchar2(1) := chr(10);
 gc_lf   varchar2(1) := chr(13);
 gc_crlf varchar2(2) := gc_cr||gc_lf;
 gc_lfcr varchar2(2) := gc_lf||gc_cr;

 GC_MAX_LINE_NUMBER number := 20000;

 TYPE LINES_TYPE is TABLE OF varchar2(32767) index by PLS_INTEGER;

 procedure getProgress(p_result_id number) is
 v_msg ac_results_header.msg%TYPE;
 begin
     begin
       select rh.msg
       into v_msg
       from ac_results_header rh
       where rh.result_id = p_result_id;
     exception when NO_DATA_FOUND then
       v_msg := null;
     end;
     htp.p(v_MSG);
 end getProgress;

 procedure setCancel(p_result_id number, p_val varchar2) is
 pragma autonomous_transaction;
 begin
  update ac_results_header
     set is_cancel = p_val
   where result_id = p_result_id;

  commit;
 end;

 procedure setMsgValue(p_result_id number, p_val varchar2) is
 pragma autonomous_transaction;
 begin
  update ac_results_header h
   set h.msg = p_val
   where h.result_id = p_result_id;

   commit;
 end  setMsgValue;

 function isCancel(p_result_id number) return boolean is
 v_is_cancel ac_results_header.is_cancel%TYPE;
 begin
  begin
    select h.is_cancel 
    into v_is_cancel
    from  ac_results_header h
    where h.result_id =  p_result_id;   
  exception when NO_DATA_FOUND or TOO_MANY_ROWS then
    v_is_cancel := 'N';
  end;
  
  return v_is_cancel = 'Y';
 end  isCancel;

 function getRegionId(p_static_region_id varchar2) return varchar2 is
 l_region_id number;
 begin
   begin
    select
     r.region_id  into l_region_id
     from apex_application_page_regions r
     where  r.static_id = p_static_region_id
       and  r.application_id = NV('APP_ID')
       and  r.page_id = NV('APP_PAGE_ID');
   exception when NO_DATA_FOUND then
     l_region_id := null;
   end;
   return l_region_id;
 end  getRegionId;

/***************************************************************************************************
 * DTreeTable ApEx Plugin puts hierarchical data in columns, but use an indented outline structure
 * in the first column to illustrate the tree structure.
 *
 * Author: Linh Dinh (linh@dinh.de)
 * URL: http://www.dinh.de
 * Created On: 2010-11-06
 * Version: 0.9 (2010-11-06)

 * This Plugin is dual licensed under the MIT and GPL licenses:
 * http://www.opensource.org/licenses/mit-license.php
 * http://www.gnu.org/licenses/gpl.html
 **************************************************************************************************/

FUNCTION render_dtreetable(
    p_region              IN apex_plugin.t_region,
    p_plugin              IN apex_plugin.t_plugin,
    p_is_printer_friendly IN BOOLEAN )
  RETURN apex_plugin.t_region_render_result
IS
  l_col_header htmldb_application_global.vc_arr2;
  l_value_list apex_plugin_util.t_column_value_list;
  v_indenting INTEGER;
  v_width_1 INTEGER;
  v_expand CHAR(1);
  v_level_1 INTEGER;
  v_class_display VARCHAR(30);
  v_class_leaf VARCHAR(30);
  v_version VARCHAR2(10) := ''; --0.9
  v_num_header_columns number;
  v_hidden_style varchar2(100);
BEGIN
--  logger.log(p_text => 'START render_dtreetable ');
  v_indenting := p_region.attribute_01;
  l_col_header := htmldb_util.string_to_table(p_region.attribute_02, ':');
  v_expand := p_region.attribute_03;
  v_width_1 := p_region.attribute_04;


  APEX_PLUGIN_UTIL.DEBUG_REGION (
    p_plugin,
    p_region
  );

  -- Assign javascript file
  apex_javascript.add_library (
    p_name      => 'dtt',
    p_directory => p_plugin.file_prefix,
    p_version   => v_version
  );

  -- Assign css file
  apex_css.add_file (
    p_name => 'dtt_style',
    p_directory => p_plugin.file_prefix,
    p_version => v_version
  );

  -- Get resultset from query
  l_value_list := apex_plugin_util.get_data (
    p_sql_statement => p_region.source,
    p_min_columns => 2,
    p_max_columns => 50,
    p_component_name => p_region.name
  );

  -- Set level value for the first node
--   logger.log_information(p_text => 'l_value_list.COUNT='||l_value_list.COUNT);
  begin
   v_level_1 := l_value_list(1)(1);
  exception when NO_DATA_FOUND then
   htp.p('NO DATA FOUND');
   return NULL;
  end;

  -- Start print table
  htp.p('<table id="dtt_'
    || p_region.id
    || '_table" class="dtt_table">'
  );

  -- Start print thead
  IF (l_col_header.COUNT > 0) THEN
    htp.p('<thead class="dtt_thead">');
    htp.p('<tr class="dtt_head_tr">');

    FOR i IN 1 .. l_col_header.COUNT LOOP
      htp.p('<th class="dtt_head_td">' || l_col_header(i) || '</th>');
    END LOOP;

    htp.p('</tr>');
    htp.p('</thead>');
  END IF;

  v_num_header_columns  := l_col_header.COUNT;

  -- Start print for tbody
  htp.p('<tbody id="dtt_'
    || p_region.id
    || '_tbody" class="dtt_tbody">');

  FOR i IN 1 .. l_value_list(1).count
  LOOP
    -- Check wheather the settings are collapsed or expanded on start up
    IF i < l_value_list(1).count THEN
      IF v_expand = 'Y' OR l_value_list(1)(i) = v_level_1 THEN
        v_class_display := 'dtt_expanded';
      ELSE
        v_class_display := 'dtt_collapsed';
      END IF;
    ELSE
      v_class_display := 'dtt_collapsed';
    END IF;

    htp.p(
      '<tr dtt_level="'
      || htf.escape_sc(l_value_list(1)(i))
      || '" class="' || v_class_display
      || '_tr" onMouseOver="this.bgColor=''#EFEFEF'';" onMouseOut="this.bgColor=''#FFFFFF'';" rownum="'
      || i || ' "> '
    );

    IF i < l_value_list(1).count THEN
      IF v_expand = 'N' AND l_value_list(1)(i) = v_level_1 THEN
        v_class_display := 'dtt_collapsed';
      END IF;

      IF l_value_list(1)(i) < l_value_list(1)(i+1) THEN
        v_class_leaf := 'dtt_branch';
      ELSE
        v_class_leaf := 'dtt_leaf';
      END IF;
    ELSE
      v_class_leaf := 'dtt_leaf';
    END IF;

    -- Rendering level value as first col and second value into one column
    htp.p(
      '<td class="dtt_td" style="'
    );

    IF v_width_1 > 0 THEN
      htp.p('width: ' || v_width_1 || 'px; ');
    END IF;

    htp.p('" name="dtt_level">');
    htp.p(
      '<span style="margin-left:'
      || (htf.escape_sc(l_value_list(1)(i)) - 1) * v_indenting
      || 'px; ">&#160;</span>'
    );
    htp.p('<a href="#" class="dtt_toggle" onclick="dttToggleNode(''' || p_region.id || ''', ' || i || ');">');
    htp.p('<span class="dtt_icon ' || v_class_leaf || '_span ' || v_class_display || '_span">&#160;</span>');
    htp.p('</a>');
    htp.p(
   /*   sys.htf.escape_sc(l_value_list(2)(i))  */
       l_value_list(2)(i)
      || '</td>'
    );
    -- Render all other columns
    FOR j IN 3 .. l_value_list.count
    LOOP
      v_hidden_style := null;
      if ( j > v_num_header_columns + 1 ) then
        v_hidden_style := ' style="display:none;"';
      end if;
      htp.p('<td class="dtt_td" '||v_hidden_style || ' >' || htf.escape_sc(l_value_list(j)(i)) || '</td>');
    END LOOP;

    htp.p('</tr>');

  END LOOP;
  htp.p('</tbody>');
  htp.p('</table>');
  RETURN NULL;
 -- exception when OTHERS then
 --   logger.log_error(p_text => 'Error in render_dtreetable');
 --   raise;
END render_dtreetable;

   function esc(p_v varchar2) return varchar2 is
     l_v varchar2(32767);
   begin
       l_v := replace(p_v,'\','\\');
       l_v := replace(l_v,'"','\"');
       l_v := replace(l_v,'/','\/');
       l_v := replace(l_v,chr(10),' ');
       l_v := replace(l_v,chr(13),' ');
       return l_v;
   end  esc;

   function r_esc(p_r varchar2, p_s1 varchar2, p_s2 varchar2) return varchar2 is
   begin
       return replace(p_r, p_s1, esc(p_s2));
   end  r_esc;

  procedure clob_2_line_array(p_src   IN OUT NOCOPY CLOB,
                              p_lines IN OUT NOCOPY LINES_TYPE) is
   l_pos0 number;
   l_pos1 number;
   l_CR   varchar2(1) := CHR(10); -- 0A
   l_LF   varchar2(1) := CHR(13); -- 0D
   l_line varchar2(32000);
   l_line_num number;
   l_CLOB CLOB;
  begin
    l_CLOB := replace(p_src, l_LF)||l_CR;
    if l_CLOB is not null then
     l_pos0     := 1;
     l_pos1     := 1;
     l_line_num := 1;
     LOOP
      l_pos1 := instr(l_CLOB,l_CR,l_pos0);
      EXIT WHEN l_POS1 <= 0 or l_line_num >= GC_MAX_LINE_NUMBER;
      l_line := substr(l_CLOB, l_pos0, l_pos1 - l_pos0);
      p_lines(l_line_num) := l_line;

      l_pos0     := l_pos1 + 1;
      l_line_num := l_line_num + 1;
      END LOOP;
     end if;
   end clob_2_line_array;

   procedure showHeader(p_id number) is
     v_param_name varchar2(4000);
     v_app_name_1 varchar2(4000);
     v_app_name_2 varchar2(4000);
     v_help_text  varchar2(4000);
   begin
      select
       ar.param_name,
       ah.app_id_1||'('||ah.workspace_1||')' ,
       ah.app_id_2||'('||ah.workspace_1||')'
      into
       v_param_name,
       v_app_name_1,
       v_app_name_2
      from ac_results ar,
           ac_results_header ah
      where ar.result_id = ah.result_id
        and ar.id = p_id;

      begin
        select d.comments
         into  v_help_text
        from AC_RESULTS t,
            APEX_DICTIONARY d
        where t.help_text = d.apex_view_name ||'.'||d.column_name
          and t.id = p_id;
      exception when NO_DATA_FOUND then
        v_help_text := null;
      end;

      htmldb_util.set_session_state ('P60_HEADER_1',    v_app_name_1);
      htmldb_util.set_session_state ('P60_HEADER_2',    v_app_name_2);
      htmldb_util.set_session_state ('P60_PARAM_NAME',  v_param_name);
      htmldb_util.set_session_state ('P60_PARAMETER_DESCR', v_help_text);
   end showHeader;

   function showDetails(p_id number) return number is
   v_line_1       varchar2(4000);
   v_line_2       varchar2(4000);
   v_src_1        clob;
   v_src_2        clob;
   v_res_id       number;
   v_line_num_1   number;
   v_line_num_2   number;
   v_line_num_max number;
   v_lines_1      LINES_TYPE;
   v_lines_2      LINES_TYPE;
   begin
     v_res_id := acc_seq.nextval;

     select nvl(r.src_val_1, to_clob(r.param_val_1)) ,
            nvl(r.src_val_2, to_clob(r.param_val_2))
      into  v_src_1, v_src_2
     from ac_results r
     where r.id = p_id;

      htmldb_util.set_session_state ('P60_LENGTH_1', dbms_lob.getlength(v_src_1));
      htmldb_util.set_session_state ('P60_LENGTH_2', dbms_lob.getlength(v_src_2));

     v_src_1 := replace(replace(v_src_1, gc_crlf, gc_cr), gc_lfcr, gc_cr);
     v_src_2 := replace(replace(v_src_2, gc_crlf, gc_cr), gc_lfcr, gc_cr);

     clob_2_line_array(v_src_1, v_lines_1);
     clob_2_line_array(v_src_2, v_lines_2);

     v_line_num_1 := v_lines_1.count;
     v_line_num_2 := v_lines_2.count;

     if v_line_num_1 > v_line_num_2 then
      v_line_num_max := v_line_num_1;
     else
      v_line_num_max := v_line_num_2;
     end if;

     for l_num in 1..v_line_num_max loop
         if l_num <= v_line_num_1 then
           v_line_1 := v_lines_1(l_num);
         else
           v_line_1 := null;
         end if;

         if l_num <= v_line_num_2 then
           v_line_2 := v_lines_2(l_num);
         else
           v_line_2 := null;
         end if;

         insert into ac_results_details
         (id, res_id,    line_1,   line_2,  line_num)
         values
         (acc_seq.nextval,
              v_res_id, v_line_1,v_line_2,  l_num   );

     end loop;

     return v_res_id;
   end showDetails;

   function htmlStr(v_str varchar2) return varchar2 is
   begin
     return htf.escape_sc(v_str);
   end htmlStr;

end app_apex;
/

